/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package palma.resources;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.ws.rs.Consumes;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import palma.json.CustomerMemberJson;
import palma.pojo.CustomerMember;

/**
 *
 * @author nimda
 */
@Path("/stokies")
public class StokiesResource {
    
    private static final Logger LOG = LoggerFactory.getLogger(StokiesResource.class);    
    
   Connection conn = null;
   
   public StokiesResource(Connection conn) {
       this.conn = conn;
   }
   
   @POST
    @Consumes({ MediaType.APPLICATION_JSON})
    @Produces({ MediaType.APPLICATION_JSON})
    public String create(CustomerMemberJson json) {
        DSLContext create = DSL.using(conn, SQLDialect.MYSQL);       
        LOG.info("id {}", json.getId_no());
        LOG.info("id {}", json.getMember_no());
        LOG.info("id {}", json.getMember_type());
        LOG.info("id {}", json.getName());
        
        Object maxscoreID = create
               .select(DSL.fieldByName(CustomerMember.tableName,CustomerMember.member_no).max().add(1))
               .from(DSL.tableByName(CustomerMember.tableName))
               .fetchOne(0) ;
        
        maxscoreID = maxscoreID != null ? maxscoreID : "1";          
        
        Record r = create.insertInto(DSL.tableByName(CustomerMember.tableName), 
                DSL.fieldByName(CustomerMember.tableName,CustomerMember.id_no),
                DSL.fieldByName(CustomerMember.tableName,CustomerMember.member_no),
                DSL.fieldByName(CustomerMember.tableName,CustomerMember.member_type),
                DSL.fieldByName(CustomerMember.tableName,CustomerMember.name),
                DSL.fieldByName(CustomerMember.tableName,CustomerMember.email),
                DSL.fieldByName(CustomerMember.tableName,CustomerMember.address1),
                DSL.fieldByName(CustomerMember.tableName,CustomerMember.address2),
                DSL.fieldByName(CustomerMember.tableName,CustomerMember.address3),
                DSL.fieldByName(CustomerMember.tableName,CustomerMember.postcode),
                DSL.fieldByName(CustomerMember.tableName,CustomerMember.ctry_state),
                DSL.fieldByName(CustomerMember.tableName,CustomerMember.reg_date))
                .values(json.getId_no(), 
                        maxscoreID, 
                        json.getMember_type(),
                        json.getName(),
                        json.getEmail(),
                        json.getAddress1(),
                        json.getAddress2(),
                        json.getAddress3(),
                        json.getPostcode(),
                        json.getCtry_state(),
                        new Date()).returning().fetchOne();
        
        return String.valueOf(maxscoreID);
    }
    
    @GET
    @Produces({ MediaType.APPLICATION_JSON})
    public List<CustomerMemberJson> findAll() {
        DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
        
        List<CustomerMemberJson> senarai = new ArrayList<CustomerMemberJson>();
        
        Result<Record> result = create.select()
                .from(DSL.tableByName(CustomerMember.tableName))
                 .fetch();
        
        for (Record r : result) {
            int i = 0;
            CustomerMemberJson j = new CustomerMemberJson();
            
            LOG.info("member no {}" , String.valueOf(r.getValue( CustomerMember.member_no)));
            LOG.info("name {}" , String.valueOf(r.getValue( CustomerMember.name)));
            j.setMember_no(String.valueOf(r.getValue( CustomerMember.member_no)));
            j.setAddress1(String.valueOf(r.getValue( CustomerMember.address1)));
            j.setAddress2(String.valueOf(r.getValue( CustomerMember.address2)));
            j.setAddress3(String.valueOf(r.getValue( CustomerMember.address3)));
            j.setPostcode(String.valueOf(r.getValue( CustomerMember.postcode)));
            j.setCtry(String.valueOf(r.getValue( CustomerMember.ctry)));
            j.setName(String.valueOf(r.getValue( CustomerMember.name)));
            j.setId_no(String.valueOf(r.getValue( CustomerMember.id_no)));
            j.setEmail(String.valueOf(r.getValue( CustomerMember.email)));  
            senarai.add(j);
            i++;
        }
        
        
//        return create.select().from(DSL.tableByName(CustomerMember.tableName))
//                 .fetch()
//                 .into(CustomerMemberJson.class);
        return senarai;
    }
    
    @GET @Path("{id}")
    @Produces({ MediaType.APPLICATION_JSON, MediaType.APPLICATION_XML })
    public CustomerMemberJson findById(@PathParam("id") String id) {
        DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
        return create.fetchOne(DSL.tableByName(CustomerMember.tableName), 
                DSL.fieldByName("member_no").equal(id)).into(CustomerMemberJson.class);
                
    }
    
    @GET
    @Path("search/{searchBy}/{value}")    
    @Produces({ MediaType.APPLICATION_JSON })
    public List<CustomerMemberJson> find(@PathParam("searchBy") String searchBy, @PathParam("value") String value) {
        DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
        
        LOG.info("searchBy {}", searchBy);
        LOG.info("value {}", value);
        
        List<CustomerMemberJson> senarai = new ArrayList<CustomerMemberJson>();
        
//        CustomerMemberJson j = create.fetchOne(DSL.tableByName(CustomerMember.tableName), 
//                DSL.fieldByName(searchBy).equal(value)).into(CustomerMemberJson.class);
        
        //map manually as there is a problem when mapping directly        
        Result<Record> r = create.fetch(DSL.tableByName(CustomerMember.tableName), 
                DSL.fieldByName(searchBy).equal(value));        
        
        if ( r.isNotEmpty() ) {
            CustomerMemberJson j = new CustomerMemberJson();
            j.setMember_no(String.valueOf(r.getValue(0, CustomerMember.member_no)));
            j.setAddress1(String.valueOf(r.getValue(0, CustomerMember.address1)));
            j.setAddress2(String.valueOf(r.getValue(0, CustomerMember.address2)));
            j.setAddress3(String.valueOf(r.getValue(0, CustomerMember.address3)));
            j.setPostcode(String.valueOf(r.getValue(0, CustomerMember.postcode)));
            j.setCtry(String.valueOf(r.getValue(0, CustomerMember.ctry)));
            j.setName(String.valueOf(r.getValue(0, CustomerMember.name)));
            j.setId_no(String.valueOf(r.getValue(0, CustomerMember.id_no)));
            j.setEmail(String.valueOf(r.getValue(0, CustomerMember.email)));   
            senarai.add(j);
        }
        
        
//        SelectQuery query = create.selectQuery();
//        query.addFrom(DSL.tableByName(CustomerMember.tableName));
//        
//        if (searchBy != null && !("").equals(searchBy)) 
//            query.addConditions(DSL.fieldByName(searchBy).eq(value));
//        
//        
//        
//        
//        LOG.info("query {}", query.getSQL());
//        
//        Result r = query.fetch();
//        
//        LOG.info("json {}", r.formatJSON());
//        
//        
//        CustomerMemberJson j = query.fetchOne().into(CustomerMemberJson.class);
//        LOG.info("member no {}", j.getMember_no());
//        
//       
        return senarai;
        
//        return create.fetchOne(DSL.tableByName(CustomerMember.tableName), 
//                DSL.fieldByName("member_no").equal(id)).into(CustomerMemberJson.class);
    }
   
    
}
